<?php
/**
* parse_tweets.php
* Populate the database with new tweet data from the json_cache table
* Latest copy of this code: http://140dev.com/free-twitter-api-source-code-library/
* @author Adam Green <140dev@gmail.com>
* @license GNU Public License
* @version BETA 0.10
*/
require_once('140dev_config.php');
require_once('db_lib.php');
$oDB = new db;

// This should run continuously as a background process
while (true) {

  // Process all new tweets
  $query = "SELECT ID_CACHE, DS_RAW_TWEET FROM TMP_TWITTER_JSON_CACHE WHERE NOT NUM_PARSED";
  $result = $oDB->select($query);
  if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
	}
  while($row = mysqli_fetch_assoc($result)) {
		
    $cache_id = $row['ID_CACHE'];
    // Each JSON payload for a tweet from the API was stored in the database  
    // by serializing it as text and saving it as base64 raw data
    $tweet_object = unserialize(base64_decode($row['DS_RAW_TWEET']));
		
    // Mark the tweet as having been parsed
    $oDB->update('TMP_TWITTER_JSON_CACHE','NUM_PARSED = true','ID_CACHE = ' . $cache_id);
		
    // Gather tweet data from the JSON object
    // $oDB->escape() escapes ' and " characters, and blocks characters that
    // could be used in a SQL injection attempt
    $tweet_id = $tweet_object->id_str;
    $tweet_text = $oDB->escape($tweet_object->text);
    $created_at = $oDB->date($tweet_object->created_at);
    if (isset($tweet_object->geo)) {
      $geo_lat = $tweet_object->geo->coordinates[0];
      $geo_long = $tweet_object->geo->coordinates[1];
    } else {
      $geo_lat = $geo_long = 0;
    } 
    $user_object = $tweet_object->user;
    $user_id = $user_object->id_str;
    $screen_name = $oDB->escape($user_object->screen_name);
    $name = $oDB->escape($user_object->name);
    $profile_image_url = $user_object->profile_image_url;
    $entities = $tweet_object->entities;
	$in_reply_to_status_id = $tweet_object->in_reply_to_status_id;
	$in_reply_to_user_id = $tweet_object->in_reply_to_user_id;

/*
	echo '<pre>';
 	print_r($tweet_object);
 	echo '</pre>';	
*/ 	
    // Add a new user row or update an existing one
    $field_values = 'DS_SCREEN_NAME = "' . $screen_name . '", ' .
      'DS_PROFILE_IMAGE_URL = "' . $profile_image_url . '", ' .
      'ID_USER_TWITTER = ' . $user_id . ', ' .
      'DS_NAME = "' . $name . '", ' .
      'DS_LOCATION = "' . $oDB->escape($user_object->location) . '", ' . 
      'DS_URL = "' . $user_object->url . '", ' .
      'DS_DESCRIPTION = "' . $oDB->escape($user_object->description) . '", ' .
      'DT_CREATED_AT = "' . $oDB->date($user_object->created_at) . '", ' .
      'NUM_FOLLOWERS_COUNT = ' . $user_object->followers_count . ', ' .
      'NUM_FRIENDS_COUNT = ' . $user_object->friends_count . ', ' .
      'NUM_STATUSES_COUNT = ' . $user_object->statuses_count . ', ' . 
      'DS_TIME_ZONE = "' . $user_object->time_zone . '", ' .
      'DT_LAST_UPDATE = "' . $oDB->date($tweet_object->created_at) . '"' ;			

    if ($oDB->in_table('MAST_TWITTER_USERS','ID_USER_TWITTER="' . $user_id . '"')) {
      $oDB->update('MAST_TWITTER_USERS',$field_values,'ID_USER_TWITTER= "' .$user_id . '"');
    } else {			
      $oDB->insert('MAST_TWITTER_USERS',$field_values);
    }
		
    // Add the new tweet
    // The streaming API sometimes sends duplicates, 
    // so test the tweet_id before inserting
    if (! $oDB->in_table('MAST_TWITTER_TWEETS','ID_TWEET=' . $tweet_id )) {
		
      // The entities JSON object is saved with the tweet
      // so it can be parsed later when the tweet text needs to be linkified
      $field_values = 'ID_TWEET = ' . $tweet_id . ', ' .
        'DS_TWEET_TEXT = "' . $tweet_text . '", ' .
        'DT_CREATED_AT = "' . $created_at . '", ' .
        'NUM_GEO_LAT = ' . $geo_lat . ', ' .
        'NUM_GEO_LONG = ' . $geo_long . ', ' .
        'ID_USER = ' . $user_id . ', ' .				
        'DS_SCREEN_NAME = "' . $screen_name . '", ' .
        'DS_NAME = "' . $name . '", ' .
        'DS_ENTITIES ="' . base64_encode(serialize($entities)) . '", ' .
        'DS_PROFILE_IMAGE_URL = "' . $profile_image_url . '"';
			
      $oDB->insert('MAST_TWITTER_TWEETS',$field_values);
    }
		
    // The mentions, tags, and URLs from the entities object are also
    // parsed into separate tables so they can be data mined later
    foreach ($entities->user_mentions as $user_mention) {
		
      $where = 'ID_TWEET=' . $tweet_id . ' ' .
        'AND ID_SOURCE_USER=' . $user_id . ' ' .
        'AND ID_TARGET_USER=' . $user_mention->id;		
					 
      if(! $oDB->in_table('MAST_TWITTER_MENTIONS',$where)) {
			
        $field_values = 'ID_TWEET=' . $tweet_id . ', ' .
        'ID_SOURCE_USER=' . $user_id . ', ' .
        'ID_TARGET_USER=' . $user_mention->id;	
				
        $oDB->insert('MAST_TWITTER_MENTIONS',$field_values);
      }
    }
    foreach ($entities->hashtags as $hashtag) {
			
      $where = 'ID_TWEET=' . $tweet_id . ' ' .
        'AND DS_TAG="' . $hashtag->text . '"';		
					
      if(! $oDB->in_table('MAST_TWITTER_TAGS',$where)) {
			
        $field_values = 'ID_TWEET=' . $tweet_id . ', ' .
          'DS_TAG="' . $hashtag->text . '"';	
				
        $oDB->insert('MAST_TWITTER_TAGS',$field_values);
      }
    }
    foreach ($entities->urls as $url) {
		
      if (empty($url->expanded_url)) {
        $url = $url->url;
      } else {
        $url = $url->expanded_url;
      }
			
      $where = 'ID_TWEET=' . $tweet_id . ' ' .
        'AND DS_URL="' . $url . '"';		
					
      if(! $oDB->in_table('MAST_TWITTER_URLS',$where)) {
        $field_values = 'ID_TWEET=' . $tweet_id . ', ' .
          'DS_URL="' . $url . '"';	
				
        $oDB->insert('MAST_TWITTER_URLS',$field_values);
      }
    }
    
    if ($oDB->in_table('MAST_TWITTER_ACCOUNTS','ID_USER_TW="' . $in_reply_to_user_id . '"')) {
    	$field_values = 'ID_TWEET=' . $tweet_id . ', ' .
        'ID_SOURCE_USER=' . $user_id . ', ' .
        'ID_TARGET_TWEET=' . $in_reply_to_status_id;	
      	$oDB->insert('MAST_TWITTER_REPLIES',$field_values);
    }
    
    // Si es un retweet
    if (isset($tweet_object->retweeted_status)) {
		
		guardar_retweet($tweet_object->retweeted_status, $user_id, $oDB);
	}
    
    
    		
  } 
		
  // You can adjust the sleep interval to handle the tweet flow and 
  // server load you experience
  sleep(60);
}

function guardar_retweet($retweet, $target_user, $oDB){
	$tweet_id = $retweet->id_str;
    $tweet_text = $oDB->escape($retweet->text);
    $created_at = $oDB->date($retweet->created_at);
    if (isset($retweet->geo)) {
      $geo_lat = $retweet->geo->coordinates[0];
      $geo_long = $retweet->geo->coordinates[1];
    } else {
      $geo_lat = $geo_long = 0;
    } 
    $user_object = $retweet->user;
    $user_id = $user_object->id_str;
    $screen_name = $oDB->escape($user_object->screen_name);
    $name = $oDB->escape($user_object->name);
    $profile_image_url = $user_object->profile_image_url;
    
    // Add a new user row or update an existing one
    $field_values = 'DS_SCREEN_NAME = "' . $screen_name . '", ' .
      'DS_PROFILE_IMAGE_URL = "' . $profile_image_url . '", ' .
      'ID_USER_TWITTER = ' . $user_id . ', ' .
      'DS_NAME = "' . $name . '", ' .
      'DS_LOCATION = "' . $oDB->escape($user_object->location) . '", ' . 
      'DS_URL = "' . $user_object->url . '", ' .
      'DS_DESCRIPTION = "' . $oDB->escape($user_object->description) . '", ' .
      'DT_CREATED_AT = "' . $oDB->date($user_object->created_at) . '", ' .
      'NUM_FOLLOWERS_COUNT = ' . $user_object->followers_count . ', ' .
      'NUM_FRIENDS_COUNT = ' . $user_object->friends_count . ', ' .
      'NUM_STATUSES_COUNT = ' . $user_object->statuses_count . ', ' . 
      'DS_TIME_ZONE = "' . $user_object->time_zone . '", ' .
      'DT_LAST_UPDATE = "' . $oDB->date($retweet->created_at) . '"' ;			

    if ($oDB->in_table('MAST_TWITTER_USERS','ID_USER_TWITTER="' . $user_id . '"')) {
      $oDB->update('MAST_TWITTER_USERS',$field_values,'ID_USER_TWITTER= "' .$user_id . '"');
    } else {			
      $oDB->insert('MAST_TWITTER_USERS',$field_values);
    }
    
    // Inserta en la tabla de retweets
    $field_values = "ID_TWEET = '" . $tweet_id . "', ID_SOURCE_USER= '" . $user_id . "', ID_TARGET_USER='" . $target_user . "'";
    $oDB->insert('MAST_TWITTER_RETWEETS',$field_values);
    
}
?>